<?php


namespace app\common\library;

use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;


class Excel
{
    public function downloadExcel($name, $data = [], $head = [], $keys = [])
    {
        $count = count($head);  //计算表头数量
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        for ($i = 0; $i < $count ; $i++) {     //循环设置表头：
            $sheet->getCell($this->convertNumToExcelColumnName($i) . '1')->getStyle()->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
            $sheet->getCell($this->convertNumToExcelColumnName($i) . '1')->setValueExplicit($head[$i],DataType::TYPE_STRING);
        }
        //循环设置单元格：
        foreach ($data as $key => $item) {
            //$key+2,因为第一行是表头，所以写到表格时，从第二行开始写
            for ($i = 0; $i < $count; $i++) {
                if(isset($item[$keys[$i]])){
                    $sheet->getCell($this->convertNumToExcelColumnName($i) . ($key + 2))->getStyle()->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
                    $sheet->getCell($this->convertNumToExcelColumnName($i) . ($key + 2))->setValueExplicit($item[$keys[$i]],DataType::TYPE_STRING);
                     //固定列宽
                    $spreadsheet->getActiveSheet()->getColumnDimension($this->convertNumToExcelColumnName($i))->setWidth(20);
                }
               
            }

        }
        $names = $name;
        //utf-8转unicode格式
        $name = iconv('UTF-8', 'UCS-2BE', $name);
        $len = strlen($name);

        $str = '';

        for ($i = 0; $i < $len - 1; $i = $i + 2) {

            $c = $name[$i];

            $c2 = $name[$i + 1];

            if (ord($c) > 0) {

                $str .= '\u' . base_convert(ord($c), 10, 16) . str_pad(base_convert(ord($c2), 10, 16), 2, 0, STR_PAD_LEFT);

            } else {

                $str .= '\u' . str_pad(base_convert(ord($c2), 10, 16), 4, 0, STR_PAD_LEFT);

            }

        }

//        header('Content-Type: application/vnd.ms-excel');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

        header('Data-Type: binary');
        //前端导出数据根据这个unicode格式解析为中文
        header('Data-Filename: ' . $str);
        header('Content-Disposition: attachment;filename="' . $names . '.xlsx"');
        header('Cache-Control: max-age=0');
        header('Access-Control-Expose-Headers:Data-Type,Data-Filename');

        //header('Content-Type: application/vnd.ms-excel');
        //header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        //header('Cache-Control: max-age=0');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');

    }

    private function convertNumToExcelColumnName($num){ //转换数字列到Excel列名，支持到第ZZ列
        $ch='';
        if ($num>=26){
            $ch=strtoupper(chr(floor($num/26)+65-1));
            $num=$num%26;
        }
        $ch.=strtoupper(chr($num+65));
        return $ch;
    }
}